package movie.util;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * JDBC 操作 Hive（注：JDBC 访问 Hive 前需要先启动HiveServer2）
 * 单线程
 */
public class HiveJDBCUtil {

    private static String driverName = "org.apache.hive.jdbc.HiveDriver";
    private static String url = "jdbc:hive2://master:10001";
    private static String user = "hadoop";
    private static String password = "";

    private static Connection conn = null;
    private static Statement stmt = null;
    private static ResultSet rs = null;

    // 加载驱动、创建连接

    public static void init() throws Exception {
        Class.forName(driverName);
        conn = DriverManager.getConnection(url,user,password);
        stmt = conn.createStatement();
    }

    // 创建数据库

    public static void createDatabase(String dataBase) throws Exception {
        String sql = "create database IF NOT EXISTS "+dataBase;
        System.out.println("Running: " + sql);
        init();
        stmt.execute(sql);
        destory();
    }

    // 查询所有数据库
    public static void showDatabases() throws Exception {
        String sql = "show databases";
        System.out.println("Running: " + sql);
        init();
        rs = stmt.executeQuery(sql);
        while (rs.next()) {
            System.out.println(rs.getString(1));
        }
        destory();
    }
    // 创建表
    public static void createTable(String sql) throws Exception {
        System.out.println("Running: " + sql);
        init();
        stmt.execute(sql);
        destory();
    }

    // 查询所有表
    public static void showTables() throws Exception {
        String sql = "show tables";
        System.out.println("Running: " + sql);
        init();
        rs = stmt.executeQuery(sql);
        while (rs.next()) {
            System.out.println(rs.getString(1));
        }
        destory();
    }

    // 查看表结构

    public static void descTable(String talble) throws Exception {
        String sql = "desc "+talble;
        System.out.println("Running: " + sql);
        init();
        rs = stmt.executeQuery(sql);
        while (rs.next()) {
            System.out.println(rs.getString(1) + "\t" + rs.getString(2));
        }
        destory();
    }

    // 加载数据
    public static void loadData(String filePath,String tableName) throws Exception {
        //String sql = "load data local inpath '" + filePath + "' overwrite into table "+tableName;
        String sql = "LOAD DATA inpath '" + filePath + "' overwrite into table "+tableName;
        init();
        System.out.println("Running: " + sql);
        stmt.execute(sql);
        destory();
    }

    // 加载数据
    public static void execute(String sql) throws Exception {
        init();
        System.out.println("Running: " + sql);
        stmt.execute(sql);
        destory();
    }


    // 查询数据
    public static ResultSet selectData(String sql) throws Exception {

        System.out.println("Running: " + sql);
        init();
        rs = stmt.executeQuery(sql);
        System.out.println("员工编号" + "\t" + "员工姓名" + "\t" + "工作岗位");
        destory();
        return rs;
       /* while (rs.next()) {
            System.out.println(rs.getString("empno") + "\t\t" + rs.getString("ename") + "\t\t" + rs.getString("job"));
        }*/
    }

    // 统计查询（会运行mapreduce作业）

    public void countData() throws Exception {
        String sql = "select count(1) from emp";
        System.out.println("Running: " + sql);
        init();
        rs = stmt.executeQuery(sql);
        while (rs.next()) {
            System.out.println(rs.getInt(1) );
        }
        destory();
    }

    // 删除数据库

    public static void dropDatabase(String database) throws Exception {
        String sql = "drop database if exists "+database;
        init();
        stmt.execute(sql);
        destory();
    }

    // 删除数据库表
    public static void deopTable(String table) throws Exception {
        String sql = "drop table if exists "+table;
        System.out.println("Running: " + sql);
        init();
        stmt.execute(sql);
        destory();
    }

    // 释放资源
    public static void destory() throws Exception {
        if ( rs != null) {
            rs.close();
        }
        if (stmt != null) {
            stmt.close();
        }
        if (conn != null) {
            conn.close();
        }
    }
}